We will be creating a financial performance report for CheeseCo (a hypothetical company which produces cheese) using their annual income statements and balance sheets. This is to demonstrate the capabilities of a Python package I have built from scratch: finModel (for more details about the package please go through the README.md in the root directory)
root_dir = 'C:/Users/ssmal/learning/Projects/py-packages/financial_model'
file_loc = f'{root_dir}/data/cheesco-source-data-2016-2018.xlsx'
actual_dates = ['2016-12-31','2017-12-31','2018-12-31']
forecast_dates = ['2019-12-31','2020-12-31','2021-12-31','2022-12-31','2023-12-31']
from finModel.viz.plots import plot_ebitda_revenue_ratio, plot_unlevered_cashflows, plot_ebitda_component
from finModel.viz.plots import plot_days_component, plot_wc_component, plot_enterprise_vals
from finModel.statements.main import FinancialStatement
from finModel.analysis.dcf_valuation import DCFValuation
from extract import source_balance, source_income
from finModel.analysis.ratios import FinRatio
from finModel.viz.generic import ChartLabels, style_statement, style_table
from typing import List
import plotly.graph_objects as go
import pandas as pd
import numpy as np
%load_ext autoreload
%aimport finModel.analysis.ratios, finModel.viz.plots, finModel.viz.generic
%autoreload 1
statement = FinancialStatement(comp="Cheese Co.",
a_date=actual_dates,
f_date=forecast_dates,
inc=source_income(file_loc,actual_dates),
bs=source_balance(file_loc,actual_dates))
dcf = DCFValuation(wacc=0.1,lt_growth=0.02,fin=statement)
chart_data = FinRatio(dcf)
charts: List[go.Figure] = [
plot_ebitda_revenue_ratio(chart_ttl="Figure 1: EBITDA (% of revenue) and Revenue from sales and services",
data=chart_data.ebitda_rev_ratio),
plot_unlevered_cashflows(chart_ttl="Figure 2: Unlevered free cash flows evolution across forecast periods",
data=chart_data.ufcf_trend),
plot_ebitda_component(chart_ttl="Figure 3: Breaking up EBITDA into it's major components",
data=chart_data.ebitda_components),
plot_days_component(chart_ttl="Figure 4: Working capital development (days outstanding)",
data=chart_data.working_capital),
plot_wc_component(chart_ttl="Figure 5: Working capital development (value accumulated)",
data=chart_data.working_capital),
plot_enterprise_vals(lbl=ChartLabels(x="WACC",xlab="",y="Enterprise Value",ylab="USD in thousands",y2="Long-term growth",
ttl="Figure 6: Simulating enterprise values by various WACC and g"),
data=dcf.simulate_enterprise_val()),
plot_enterprise_vals(lbl=ChartLabels(x="CFs in Forecast period",xlab="",y="Enterprise Value",ylab="USD in thousands",
ttl="Figure 7: Simulating enterprise values by Continuing values and Forecasted CFs",
y2="Continuing Value"),
data=dcf.simulate_enterprise_val()),
]
style_statement(data=statement.income.to_pandas_df(),
f_date=statement.forecast_dates,
bold_rows=["Total revenues","Gross margin","EBITDA","EBIT","EBT","Net income"],
pct_rows=["Tax rate"])
| component | 2016A | 2017A | 2018A | 2019F | 2020F | 2021F | 2022F | 2023F |
|---|---|---|---|---|---|---|---|---|
| Revenues | 177,203 | 188,618 | 208,366 | 225,985 | 245,094 | 265,819 | 288,296 | 312,673 |
| Other revenues | 3,520 | 2,416 | 2,585 | 2,585 | 2,585 | 2,585 | 2,585 | 2,585 |
| Total revenues | 180,723 | 191,034 | 210,951 | 228,570 | 247,679 | 268,404 | 290,881 | 315,258 |
| Cost of goods sold | (142,394) | (150,572) | (157,523) | (177,613) | (192,632) | (208,920) | (226,586) | (245,746) |
| Gross margin | 38,329 | 40,462 | 53,428 | 50,957 | 55,047 | 59,483 | 64,295 | 69,513 |
| Operating expenses | (18,011) | (19,248) | (23,867) | (23,972) | (25,999) | (28,197) | (30,582) | (33,168) |
| EBITDA | 20,317 | 21,214 | 29,561 | 26,985 | 29,048 | 31,286 | 33,713 | 36,345 |
| D&A | (2,908) | (4,650) | (6,431) | (5,418) | (5,876) | (6,373) | (6,912) | (7,496) |
| EBIT | 17,410 | 16,564 | 23,130 | 21,567 | 23,172 | 24,913 | 26,801 | 28,849 |
| Interest expense | (1,266) | (2,191) | (2,664) | (2,664) | (2,664) | (2,664) | (2,664) | (2,664) |
| Extraordinary income | (318) | (864) | (320) | |||||
| EBT | 15,826 | 13,509 | 20,146 | 18,903 | 20,508 | 22,249 | 24,137 | 26,185 |
| Tax rate | (24.05%) | (22.58%) | (18.94%) | (21.85%) | (21.85%) | (21.85%) | (21.85%) | (21.85%) |
| Taxes | (3,806) | (3,050) | (3,815) | (4,131) | (4,482) | (4,862) | (5,275) | (5,723) |
| Net income | 12,020 | 10,459 | 16,331 | 14,772 | 16,026 | 17,387 | 18,862 | 20,462 |
style_statement(data=statement.balance.to_pandas_df(),
f_date=statement.forecast_dates,
bold_rows=["Total Assets","Total Liabilities & Equities"])
| component | 2016A | 2017A | 2018A | 2019F | 2020F | 2021F | 2022F | 2023F |
|---|---|---|---|---|---|---|---|---|
| Intangible assets | 5,962 | 5,840 | 5,650 | 5,650 | 5,650 | 5,650 | 5,650 | 5,650 |
| PP&E | 45,106 | 49,072 | 56,109 | 59,057 | 64,051 | 69,467 | 75,341 | 81,712 |
| Financial assets | 8,824 | 9,831 | 10,323 | 10,323 | 10,323 | 10,323 | 10,323 | 10,323 |
| Inventory | 31,167 | 36,397 | 46,212 | 44,638 | 48,413 | 52,506 | 56,946 | 61,762 |
| Trade receivable | 32,884 | 38,556 | 43,582 | 45,132 | 48,949 | 53,088 | 57,577 | 62,445 |
| Other assets | 8,842 | 11,359 | 17,603 | 14,659 | 15,898 | 17,243 | 18,701 | 20,282 |
| Cash and equivalents | 11,791 | 19,409 | 8,174 | 16,789 | 22,246 | 28,169 | 34,599 | 41,577 |
| Total Assets | 144,576 | 170,464 | 187,652 | 196,249 | 215,529 | 236,446 | 259,136 | 283,750 |
| Trade payable | 15,891 | 26,352 | 36,190 | 30,570 | 33,155 | 35,959 | 38,999 | 42,297 |
| Other liabilities | 5,969 | 5,800 | 8,473 | 7,917 | 8,587 | 9,313 | 10,100 | 10,954 |
| Financial liabilities | 41,293 | 50,645 | 51,121 | 51,121 | 51,121 | 51,121 | 51,121 | 51,121 |
| Shareholder's equity | 81,422 | 87,667 | 91,868 | 106,640 | 122,667 | 140,054 | 158,916 | 179,378 |
| Total Liabilities & Equities | 144,576 | 170,464 | 187,652 | 196,249 | 215,529 | 236,446 | 259,136 | 283,750 |
style_statement(data=statement.cash.to_pandas_df(),
f_date=statement.forecast_dates,
bold_rows=["NOPAT","Gross Cash Flow","Investments in working capital","Investments in other assets/liabilities",
"Capex","Other investments","Extraordinary items","UFCF","Net Cash Flow"],
pct_rows=["Operating tax rate"])
| component | 2016A | 2017A | 2018A | 2019F | 2020F | 2021F | 2022F | 2023F |
|---|---|---|---|---|---|---|---|---|
| EBIT | 17,410 | 16,564 | 23,130 | 21,567 | 23,172 | 24,913 | 26,801 | 28,849 |
| Operating taxes | (4,187) | (3,740) | (4,380) | (4,713) | (5,064) | (5,445) | (5,857) | (6,305) |
| Operating tax rate | (24.05%) | (22.58%) | (18.94%) | (21.85%) | (21.85%) | (21.85%) | (21.85%) | (21.85%) |
| NOPAT | 13,223 | 12,824 | 18,750 | 16,854 | 18,108 | 19,469 | 20,944 | 22,544 |
| Add-back D&A | 2,908 | 4,650 | 6,431 | 5,418 | 5,876 | 6,373 | 6,912 | 7,496 |
| Gross Cash Flow | 16,131 | 17,474 | 25,181 | 22,272 | 23,984 | 25,841 | 27,856 | 30,040 |
| Inventory (movement) | (5,230) | (9,815) | 1,574 | (3,775) | (4,094) | (4,440) | (4,815) | |
| Trade receivables (movement) | (5,672) | (5,026) | (1,551) | (3,816) | (4,139) | (4,489) | (4,869) | |
| Trade payable (movement) | 10,460 | 9,838 | (5,619) | 2,585 | 2,804 | 3,041 | 3,298 | |
| Net change in working capital | (442) | (5,003) | (5,596) | (5,006) | (5,429) | (5,888) | (6,386) | |
| Net change in other asset/liabilities | (2,686) | (3,571) | 2,388 | (570) | (618) | (671) | (727) | |
| Capex (movement) | (8,616) | (13,467) | (8,366) | (10,870) | (11,789) | (12,786) | (13,867) | |
| Other investment (movement) | (885) | (302) | ||||||
| Extra-ordinary items | (318) | (864) | (320) | |||||
| Unlevered Free Cashflows | 3,981 | 2,518 | 10,697 | 7,538 | 8,005 | 8,511 | 9,060 | |
| Interest expenses | (1,266) | (2,191) | (2,664) | (2,664) | (2,664) | (2,664) | (2,664) | (2,664) |
| Delta Taxes vs. Operating taxes | 381 | 690 | 565 | 582 | 582 | 582 | 582 | 582 |
| Delta Financial liabilities | 9,352 | 475 | ||||||
| Delta Equity (inc. dividends) | (4,214) | (12,130) | 0 | (0) | 0 | (0) | ||
| Net Cashflow | 7,618 | (11,235) | 8,615 | 5,457 | 5,923 | 6,429 | 6,978 |
style_table(df=dcf.to_pandas_df(),
bold_rows=["PV of Cash flows","PV of Continuing value","Enterprise value","Equity value"],
pct_rows=["WACC","long-term growth","PV of Cash flows (%)","PV of Continuing value (%)"])
| metric | |
|---|---|
| WACC | 10.00% |
| long-term growth | 2.00% |
| PV of Cash flows | 33,408 |
| PV of Cash flows (%) | 31.78% |
| Continuing value | 115,515 |
| PV of Continuing value | 71,726 |
| PV of Continuing value (%) | 68.22% |
| Enterprise value | 105,134 |
| (-) Financial liabilities | (51,121) |
| (+) Cash | 8,174 |
| Equity value | 62,187 |
[c.show() for c in charts]
[None, None, None, None, None, None, None]